﻿Imports OMANE.Common.UI

Partial Public Class ViewQueryBuilder

    'Dim rows As ObjectModel.ObservableCollection(Of QueryRow)
    'Dim sorts As ObjectModel.ObservableCollection(Of QuerySort)
    'Dim filters As ObjectModel.ObservableCollection(Of QueryFilter)
    'Dim originalfilters As ObjectModel.ObservableCollection(Of QueryFilter)
    'Dim sources As ObjectModel.ObservableCollection(Of QuerySource)


    Sub New()

        ' Cet appel est requis par le concepteur.
        InitializeComponent()

        ' Ajoutez une initialisation quelconque après l'appel InitializeComponent().

    End Sub


    Public Sub FormuleComboBoxSelectionChanged(sender As Object, e As System.Windows.Controls.SelectionChangedEventArgs)
        Dim cbx As Windows.Controls.ComboBox = sender

        If cbx.SelectedIndex = 0 Then
            Dim txt = New Windows.Controls.TextBox()
            ModuleDashboarding._uiservice.ShowMessage(txt, "Editeur d'expression")

        End If
    End Sub

    'Sub SetDataTable(ByVal dataTable As DataTable, ByVal workflow As SqlQueryWorkflow)
    '    If dataTable Is Nothing Then Return


    '    SourceComboBox.ItemsSource = workflow.Queries


    '    Me.rows = New ObjectModel.ObservableCollection(Of QueryRow)
    '    Me.sorts = New ObjectModel.ObservableCollection(Of QuerySort)
    '    Me.filters = New ObjectModel.ObservableCollection(Of QueryFilter)
    '    Me.originalfilters = New ObjectModel.ObservableCollection(Of QueryFilter)
    '    Me.sources = New ObjectModel.ObservableCollection(Of QuerySource)

    '    SortDataGrid.ItemsSource = sorts
    '    SelectionDataGrid.ItemsSource = rows
    '    FilterDataGrid.ItemsSource = filters
    '    OriginalFilterDataGrid.ItemsSource = originalfilters
    '    ColonnesTreeView.ItemsSource = sources


    '    Dim formulas = (From c As DataColumn In dataTable.Columns Select New QueryFormula(c.ColumnName)).ToList()

    '    formulas.Insert(0, New QueryFormula())

    '    CType(SelectionDataGrid.Columns(1), Windows.Controls.DataGridComboBoxColumn).ItemsSource = formulas

    '    CType(SortDataGrid.Columns(0), Windows.Controls.DataGridComboBoxColumn).ItemsSource = rows

    '    CType(FilterDataGrid.Columns(0), Windows.Controls.DataGridComboBoxColumn).ItemsSource = rows

    '    CType(OriginalFilterDataGrid.Columns(0), Windows.Controls.DataGridComboBoxColumn).ItemsSource = (From elt As DataColumn In dataTable.Columns Select elt.ColumnName).ToArray()

    'End Sub

    Private Function PreviewSql(mdl As ViewModelQueryBuilder) As String
        Dim fs = Function(s As QuerySort)
                     If s.Row Is Nothing Then Return ""
                     If String.IsNullOrEmpty(s.Direction) Then
                         Return s.Row.Name & " ASC "
                     End If
                     If s.Direction.ToLower().Contains("asc") Or s.Direction.ToLower() = "croissant" Then
                         Return s.Row.Name & " ASC "
                     End If
                     If s.Direction.ToLower().Contains("desc") Or s.Direction.ToLower() = "decroissant" Then
                         Return s.Row.Name & " DESC "
                     End If
                     Return ""
                 End Function

        Dim aggtosql = Function(agg As String)
                           If agg = "Somme" Then Return "sum"
                           If agg = "Moyenne" Then Return "avg"
                           If agg = "Minimum" Then Return "min"
                           If agg = "Maximum" Then Return "max"
                           If agg = "Variance" Then Return "stdev"
                           If agg = "Ecart-Type" Then Return "stdev"
                           If agg = "Nombre" Then Return "count"

                           Return ""
                       End Function

        Dim fr = Function(r As QueryRow)
                     If r.Formula Is Nothing Then Return ""
                     If r.Aggregate = "" Or r.Aggregate.ToLower() = "(aucun)" Then
                         Return "(t1." & r.Formula.Formula & ") AS " & r.Name
                     End If
                     Return "(" & aggtosql(r.Aggregate) & "(" & "t1." & r.Formula.Formula & ")) AS " & r.Name
                 End Function

        Dim opetosql = Function(ope As String)
                           If ope = "Egal" Then Return "="
                           If ope = "Different" Then Return "<>"
                           If ope = "Superieur ou Egal" Then Return ">="
                           If ope = "Superieur" Then Return ">"
                           If ope = "Inferieur ou Egal" Then Return "<="
                           If ope = "Inferieur" Then Return "<"
                           If ope = "Est Contenu Dans" Then Return "IN"
                           If ope = "Comme" Then Return ""
                           If ope = "Contient" Then Return ""
                           If ope = "Commence Par" Then Return ""
                           If ope = "Termine Par" Then Return ""

                           Return ""
                       End Function

        Dim ff = Function(f As QueryFilter)
                     If f.OriginalRow = "" And f.Row Is Nothing Then Return ""

                     If f.OriginalRow <> "" Then
                         Return "t1." & f.OriginalRow & " " & opetosql(f.Operator) & " " & f.SimpleValue
                     Else
                         Return f.Row.Name & " " & opetosql(f.Operator) & " " & f.SimpleValue
                     End If
                 End Function


        Dim selectpart = "SELECT " & If(DistinctCheckBox.IsChecked, " DISTINCT ", "") & vbCrLf & String.Join("," & vbCrLf, (From r In mdl.Rows Select "  " & fr(r)).ToArray())
        Dim frompart = "FROM [" & mdl.DataSource.DataSourceName & "] AS t1 "
        Dim sortpart = If(mdl.Sorts.Count = 0, "", "ORDER BY " & String.Join(",", (From s In mdl.Sorts Select fs(s)).ToArray()))
        Dim grouppart = ""
        Dim colnotgrouped = From r In mdl.Rows Where r.Aggregate = "" Or r.Aggregate.ToLower() = "(aucun)"

        If colnotgrouped.Count() <> mdl.Rows.Count Then
            grouppart = vbCrLf & "GROUP BY " & String.Join(",", (From elt In colnotgrouped Select elt.Name).ToArray())
        End If

        Dim originalwherepart = String.Join("  " & vbCrLf & " AND ", (From f In mdl.Originalfilters Select "(" & ff(f) & ")").ToArray())
        Dim computedwherepart = String.Join("  " & vbCrLf & " AND ", (From f In mdl.Filters Where f.Row.Aggregate = "" Or f.Row.Aggregate.ToLower() = "(aucun)" Select "(" & ff(f) & ")").ToArray())
        Dim wherepart = ""
        If originalwherepart <> "" Then
            wherepart = originalwherepart
            If computedwherepart <> "" Then
                wherepart = wherepart & " AND " & computedwherepart
            End If
        Else
            If computedwherepart <> "" Then
                wherepart = computedwherepart
            End If
        End If
        If wherepart <> "" Then
            wherepart = "WHERE " & vbCrLf & wherepart
        End If
        Dim computedhavingpart = String.Join("  " & vbCrLf & " AND ", (From f In mdl.Filters Where f.Row.Aggregate <> "" And f.Row.Aggregate.ToLower() <> "(aucun)" Select "(" & ff(f) & ")").ToArray())
        Dim havingpart = ""

        If computedhavingpart <> "" Then
            havingpart = vbCrLf & "HAVING " & vbCrLf & computedhavingpart
        End If

        Return selectpart & vbCrLf & frompart & wherepart & grouppart & havingpart & vbCrLf & sortpart

    End Function

    Private Sub PreviewSqlButton_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles PreviewSqlButton.Click

        PreviewSqlTextBox.Text = PreviewSql(DataContext)

    End Sub

    Private Sub PreviewDataButton_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles PreviewDataButton.Click
        Dim mdl As ViewModelQueryBuilder = DataContext
        PreviewDataGrid.ItemsSource = mdl.DataSource.Query(PreviewSqlTextBox.Text).DefaultView
        'Dim con = New SQLiteConnection(Project.Active.ExternalDataLocalDatabaseConnectionString)
        ''Dim dbcontext = New DbLinq.Data.Linq.DataContext(con, New DbLinq.Sqlite.SqliteVendor())
        'Dim dbcontext = New System.Data.Linq.DataContext(con)
        'Dim q = dbcontext.GetTable(Of MainTableRow)()

        'Dim cmd = dbcontext.GetCommand(From r In q Select r.Deroulement)

        'Dim o = 1
        ''   dbcontext.GetCommand
        ''Dim cmd = New SQLiteCommand()
        ''cmd.CommandText = "SELECT * FROM MAINTABLE"
        ''cmd.Connection = con
        ''con.Open()
        ''cmd.ExecuteReader()
        ''con.Close()
        ''con.Dispose()
        ''Return
        'Try
        '    Dim adapter = New SQLiteDataAdapter(PreviewSqlTextBox.Text, Project.Active.ExternalDataLocalDatabaseConnectionString)
        '    Dim result As New DataTable()
        '    adapter.Fill(result)

        '    PreviewDataGrid.ItemsSource = result.DefaultView
        'Catch ex As Exception
        '    MsgBox(ex.Message)
        'End Try

    End Sub

    Private Sub PreviewDataGrid_AutoGeneratingColumn(ByVal sender As Object, ByVal e As System.Windows.Controls.DataGridAutoGeneratingColumnEventArgs) Handles PreviewDataGrid.AutoGeneratingColumn
        Dim c = TryCast(e.Column, Windows.Controls.DataGridBoundColumn)
        If c IsNot Nothing Then
            Dim cmenu = New Windows.Controls.ContextMenu()
            Dim item = New Windows.Controls.MenuItem()
            item.Header = "Format"
            Dim path = CType(c.Binding, Windows.Data.Binding).Path.Path
            AddHandler item.Click, Sub() c.Binding = New Windows.Data.Binding(path) With {.StringFormat = InputBox("Format de la colonne")}
            cmenu.Items.Add(item)
            Dim s = New Windows.Style(GetType(Windows.Controls.Primitives.DataGridColumnHeader))
            s.Setters.Add(New Windows.Setter(Windows.Controls.Primitives.DataGridColumnHeader.ContextMenuProperty, cmenu))
            c.HeaderStyle = s
        End If
    End Sub

    'Public Function GetQueryData() As SqlQueryData
    '    Return New SqlQueryData(rows, sorts, filters, originalfilters, sources)
    'End Function
    'Public Function GetQueryDataWithResults() As SqlQueryData
    '    Dim data = New SqlQueryData(rows, sorts, filters, originalfilters, sources)
    '    data.SqlQueryText = PreviewSqlTextBox.Text
    '    data.SqlQueryData = CType(PreviewDataGrid.ItemsSource, DataView).Table
    '    Return (data)
    'End Function
    'Public Sub SetQueryData(ByVal querydata As SqlQueryData, ByVal datatable As DataTable, ByVal workflow As SqlQueryWorkflow)

    '    ColonnesTreeView.ItemsSource = datatable.Columns

    '    Me.rows = querydata.Rows
    '    Me.sorts = querydata.Sorts
    '    Me.filters = querydata.Filters
    '    Me.originalfilters = querydata.Originalfilters


    '    Dim formulas = (From c As DataColumn In datatable.Columns Select New QueryFormula(c.ColumnName)).ToList()

    '    formulas.Insert(0, New QueryFormula())

    '    CType(SelectionDataGrid.Columns(1), Windows.Controls.DataGridComboBoxColumn).ItemsSource = formulas

    '    CType(SortDataGrid.Columns(0), Windows.Controls.DataGridComboBoxColumn).ItemsSource = rows

    '    CType(FilterDataGrid.Columns(0), Windows.Controls.DataGridComboBoxColumn).ItemsSource = rows

    '    CType(OriginalFilterDataGrid.Columns(0), Windows.Controls.DataGridComboBoxColumn).ItemsSource = (From elt As DataColumn In datatable.Columns Select elt.ColumnName).ToArray()

    '    SortDataGrid.ItemsSource = sorts
    '    SelectionDataGrid.ItemsSource = rows
    '    FilterDataGrid.ItemsSource = filters
    '    OriginalFilterDataGrid.ItemsSource = originalfilters

    'End Sub

    Private Sub AddSourceButton_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs)
        'sources.Add(New QuerySource() With {.DataTable = "", .QueryData = SourceComboBox.SelectedItem})
    End Sub

    Private Sub ViewQueryBuilder_DataContextChanged(sender As Object, e As Windows.DependencyPropertyChangedEventArgs) Handles Me.DataContextChanged
        Dim vm = TryCast(e.NewValue, ViewModelQueryBuilder)

        If vm IsNot Nothing Then

            If vm.DataSource IsNot Nothing Then
                CType(SelectionDataGrid.Columns(1), Windows.Controls.DataGridComboBoxColumn).ItemsSource = vm.DefaultFormulas

                CType(SortDataGrid.Columns(0), Windows.Controls.DataGridComboBoxColumn).ItemsSource = vm.Rows

                CType(FilterDataGrid.Columns(0), Windows.Controls.DataGridComboBoxColumn).ItemsSource = vm.Rows

                CType(OriginalFilterDataGrid.Columns(0), Windows.Controls.DataGridComboBoxColumn).ItemsSource = _
                    (From elt In vm.DataSource.Columns Select elt.Name).ToArray()

            End If

        End If
    End Sub
End Class

Public Class ViewModelQueryBuilder
    Implements IViewModelTabSection


    Sub New()
        If ModuleDashboarding._activeproject.DataSourceList.Count > 0 Then

            Dim cbx = New Windows.Controls.ComboBox()
            cbx.ItemsSource = ModuleDashboarding._activeproject.DataSourceList
            cbx.DisplayMemberPath = "Name"
            cbx.VerticalAlignment = Windows.VerticalAlignment.Center
            cbx.IsReadOnly = True
            ModuleDashboarding._uiservice.ShowMessage(cbx, "Selectionner une table")

            DataSource = cbx.SelectedItem

            DefaultFormulas = New ObjectModel.ObservableCollection(Of QueryFormula)( _
                From c In DataSource.Columns Select New QueryFormula(c.Name))

            'DefaultFormulas.Insert(0, New QueryFormula())
        End If
    End Sub
    Public Property DataSource As DataSource

    Public Property DefaultFormulas As New ObjectModel.ObservableCollection(Of QueryFormula)

    Public Property Rows As New ObjectModel.ObservableCollection(Of QueryRow)

    Public Property Sorts As New ObjectModel.ObservableCollection(Of QuerySort)

    Public Property Filters As New ObjectModel.ObservableCollection(Of QueryFilter)

    Public Property Originalfilters As New ObjectModel.ObservableCollection(Of QueryFilter)

    Public Property Distinct As Boolean

    Public Property Icone As String Implements IViewModelTabSection.Icone

    Public Property Title As String Implements IViewModelTabSection.Title

    Public Property IsClosable As Boolean Implements IViewModelTabSection.IsClosable
End Class